<?php
	class TutorModel extends Model {
		function TutorModel()
		{
			parent::Model();			
		}
		
		function addFromC($data, $bd)
		{
            $this->db->set('birthday', "str_to_date('".$bd."', '%d/%m/%Y')", FALSE);
            $this->db->set('create_by', "'System-TL'", FALSE);
            $this->db->set('create_date', 'NOW()', FALSE);
            $this->db->set('is_active', '1', FALSE);  
			$this->db->insert('tutor', $data);
			return $this->db->insert_id();
		}
		
		function updateFromC($data)
		{
			$this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $data); 
		}
		
		function add($data)
		{
			$dataDB = array(
				'username' => $data['username'] ,
				'password' => $data['password'] ,
               	'title' => $data['title'] ,
				'first_name_th' => $data['first_name_th'] ,
				'last_name_th' => $data['last_name_th'] ,
				'first_name_en' => $data['first_name_en'] ,
				'last_name_en' => $data['last_name_en'] ,
				'nick_name' => $data['nick_name'] ,
				'citizen_id' => $data['citizen_id'] ,
				'comment' => $data['comment'] ,
				'isBlacklist' => $data['isBlacklist'] ,
				'blackList_comment' => $data['blackList_comment'] ,
				'edu_status' => $data['edu_status'] ,
				'edu_current_level' => $data['edu_current_level'] ,
				'edu_current_university' => $data['edu_current_university'] ,
				'edu_current_department' => $data['edu_current_department'] ,
				'edu_current_student_id' => $data['edu_current_student_id'] ,
				'edu_current_class' => $data['edu_current_class'] ,
				'edu_current_GPA' => $data['edu_current_GPA'] ,
				'edu_pratom_school' => $data['edu_pratom_school'] ,
				'edu_pratom_major' => $data['edu_pratom_major'] ,
				'edu_pratom_student_id' => $data['edu_pratom_student_id'] ,
				'edu_pratom_GPA' => $data['edu_pratom_GPA'] ,
				'edu_mo_tom_school' => $data['edu_mo_tom_school'] ,
				'edu_mo_tom_major' => $data['edu_mo_tom_major'] ,
				'edu_mo_tom_student_id' => $data['edu_mo_tom_student_id'] ,
				'edu_mo_tom_GPA' => $data['edu_mo_tom_GPA'] ,
				'edu_mo_pay_school' => $data['edu_mo_pay_school'] ,
				'edu_mo_pay_major' => $data['edu_mo_pay_major'] ,
				'edu_mo_pay_student_id' => $data['edu_mo_pay_student_id'] ,
				'edu_mo_pay_GPA' => $data['edu_mo_pay_GPA'] ,
				'edu_bachelor_university' => $data['edu_bachelor_university'] ,
				'edu_bachelor_major' => $data['edu_bachelor_major'] ,
				'edu_bachelor_student_id' => $data['edu_bachelor_student_id'] ,
				'edu_bachelor_GPA' => $data['edu_bachelor_GPA'] ,
				'edu_master_university' => $data['edu_master_university'] ,
				'edu_master_major' => $data['edu_master_major'] ,
				'edu_master_student_id' => $data['edu_master_student_id'] ,
				'edu_master_GPA' => $data['edu_master_GPA'] ,
				'edu_doctor_university' => $data['edu_doctor_university'] ,
				'edu_doctor_major' => $data['edu_doctor_major'] ,
				'edu_doctor_student_id' => $data['edu_doctor_student_id'] ,
				'edu_doctor_GPA' => $data['edu_doctor_GPA'] ,
			   	'create_by' => $this->session->userdata('username'),
				'source' => $data['source'] ,
			   	'is_active' => 1
            );
            if($data['birthday'] != null)
            	$this->db->set('birthday', "str_to_date('".$data['birthday']."', '%d/%m/%Y')", FALSE);
            if($data['user_expire'] != null)
            	$this->db->set('user_expire', "str_to_date('".$data['user_expire']."', '%d/%m/%Y')", FALSE);
            $this->db->set('create_date', 'NOW()', FALSE); 
			$this->db->insert('tutor', $dataDB);
			return $this->db->insert_id();
		}
		
		
		function updateInfo($data)
		{			
			$dataDB = array(
              'username' => $data['username'] ,
				'password' => $data['password'] ,
               	'title' => $data['title'] ,
				'first_name_th' => $data['first_name_th'] ,
				'last_name_th' => $data['last_name_th'] ,
				'first_name_en' => $data['first_name_en'] ,
				'last_name_en' => $data['last_name_en'] ,
				'nick_name' => $data['nick_name'] ,
				'citizen_id' => $data['citizen_id'] ,
				'comment' => $data['comment'] ,
				'isBlacklist' => $data['isBlacklist'] ,
				'blackList_comment' => $data['blackList_comment'] ,
				'edu_status' => $data['edu_status'] ,
				'edu_current_level' => $data['edu_current_level'] ,
				'edu_current_university' => $data['edu_current_university'] ,
				'edu_current_department' => $data['edu_current_department'] ,
				'edu_current_student_id' => $data['edu_current_student_id'] ,
				'edu_current_class' => $data['edu_current_class'] ,
				'edu_current_GPA' => $data['edu_current_GPA'] ,
				'edu_pratom_school' => $data['edu_pratom_school'] ,
				'edu_pratom_major' => $data['edu_pratom_major'] ,
				'edu_pratom_student_id' => $data['edu_pratom_student_id'] ,
				'edu_pratom_GPA' => $data['edu_pratom_GPA'] ,
				'edu_mo_tom_school' => $data['edu_mo_tom_school'] ,
				'edu_mo_tom_major' => $data['edu_mo_tom_major'] ,
				'edu_mo_tom_student_id' => $data['edu_mo_tom_student_id'] ,
				'edu_mo_tom_GPA' => $data['edu_mo_tom_GPA'] ,
				'edu_mo_pay_school' => $data['edu_mo_pay_school'] ,
				'edu_mo_pay_major' => $data['edu_mo_pay_major'] ,
				'edu_mo_pay_student_id' => $data['edu_mo_pay_student_id'] ,
				'edu_mo_pay_GPA' => $data['edu_mo_pay_GPA'] ,
				'edu_bachelor_university' => $data['edu_bachelor_university'] ,
				'edu_bachelor_major' => $data['edu_bachelor_major'] ,
				'edu_bachelor_student_id' => $data['edu_bachelor_student_id'] ,
				'edu_bachelor_GPA' => $data['edu_bachelor_GPA'] ,
				'edu_master_university' => $data['edu_master_university'] ,
				'edu_master_major' => $data['edu_master_major'] ,
				'edu_master_student_id' => $data['edu_master_student_id'] ,
				'edu_master_GPA' => $data['edu_master_GPA'] ,
				'edu_doctor_university' => $data['edu_doctor_university'] ,
				'edu_doctor_major' => $data['edu_doctor_major'] ,
				'edu_doctor_student_id' => $data['edu_doctor_student_id'] ,
				'edu_doctor_GPA' => $data['edu_doctor_GPA'] ,
			  	'update_by' => $this->session->userdata('username')
            );
           if($data['birthday'] != null)
            	$this->db->set('birthday', "str_to_date('".$data['birthday']."', '%d/%m/%Y')", FALSE);
            if($data['user_expire'] != null)
            	$this->db->set('user_expire', "str_to_date('".$data['user_expire']."', '%d/%m/%Y')", FALSE);
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $dataDB); 
		}
		
		function updateProfile($data) {
			$this->db->set('update_by', "'".$this->session->userdata('username')."'", FALSE);
			$this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $data);
		}
		
		function updateTutorAddress($data)
		{			
			$dataDB = array(
              	'email' => $data['email'] ,
				'telephone_home' => $data['telephone_home'] ,
				'mobile01' => $data['mobile01'] ,
				'mobile01_network' => $data['mobile01_network'] ,
				'mobile02' => $data['mobile02'] ,
				'mobile02_network' => $data['mobile02_network'] ,
				'add_room_no' => $data['add_room_no'] ,
				'add_home_no' => $data['add_home_no'] ,
				'add_building_name' => $data['add_building_name'] ,
				'add_moo' => $data['add_moo'] ,
				'add_soi' => $data['add_soi'] ,
				'add_road' => $data['add_road'] ,
				'add_distinct' => $data['add_distinct'] ,
				'add_area' => $data['add_area'] ,
				'add_province' => $data['add_province'] ,
				'add_post_code' => $data['add_post_code'] ,
				'add_note' => $data['add_note'] ,
				'update_by' => $this->session->userdata('username')
            );
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $dataDB); 
		}
		
		function updateTutorResumePath($data)
		{			
			$dataDB = array(
              'hist_resume_path' => $data['hist_resume_path'] ,
            );
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $dataDB); 
		}
		
		function updateTutorImagePath($data)
		{			
			$dataDB = array(
              'tutor_image' => $data['tutor_image'] ,
            );
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $dataDB); 
		}
		
		function updateTutorAddImagePath($data)
		{			
			$dataDB = array(
              'add_map' => $data['add_map'] ,
            );
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $dataDB); 
		}
		
		function getAll()
		{
			$this->db->order_by("first_name_th", "asc"); 
			$this->db->order_by("last_name_th", "asc");
			$query = $this->db->get_where('tutor', array('is_active' => 1));
			return $query->result();
		}
		
		function getAllLimit($limit = NULL, $offset = NULL)
		{
			$this->db->limit($limit, $offset);			
			$this->db->order_by("first_name_th", "asc"); 
			$this->db->order_by("last_name_th", "asc");
			$query = $this->db->get_where('tutor', array('is_active' => 1));
			return $query->result();
		}
		
		function count_all()
	 	{
	 		$query = $this->db->getwhere('tutor', array('is_active'=>1));
			$count = $query->num_rows(); 
	  		return $count;
	 	}
	 	
	 	
		
		function getByID($id)
		{
			$query = $this->db->get_where('tutor', array('tutor_id' => $id, 'is_active' => 1));
			return $query->row_array();
		}
		
		function getByUserName($id)
		{
			$query = $this->db->get_where('tutor', array('username' => $id, 'is_active' => 1));
			return $query->row_array();
		}
		
		function getByNameLike($key)
		{
			$this->db->like('first_name_th', $key); 
			$this->db->order_by("first_name_th", "asc"); 
			$this->db->order_by("last_name_th", "asc"); 
			$query = $this->db->get_where('tutor', array('is_active' => 1));
			return $query->result();
		}
		
		function getBlackList($key)
		{
			$sql = "SELECT `tutor_id`,`first_name_th`,`last_name_th`,`mobile01`,`create_by`, `create_date`, `tutor_image`,`isBlackList`, `blackList_comment` 
					from tutor 
					where (`isBlackList` = 'Y' and is_active = '1') ";
			if($key['name'] != null)
				$sql .= " and (first_name_th like '%".$key['name']."%' or last_name_th like '%".$key['name']."%' )";
			if($key['tel'] != null)
				$sql .= " and (mobile01 like '%".$key['tel']."%' )";	
			if($key['email'] != null)
				$sql .= " and (email like '%".$key['email']."%' )";
			$sql .= " order by first_name_th, last_name_th ";	
				
			$query = $this->db->query($sql);  
			return $query->result();
		}
		
		function getAllFromFrontEnd()
		{
			$this->db->order_by("create_date", "asc");
			$this->db->where('status is null ', NULL, FALSE); 
			$query = $this->db->get_where('tutor', array('is_active' => 1, 'source !=' => 'backend'));
			
			return $query->result();
		}
		function tutorSearch($key)
		{
//			$this->output->enable_profiler(TRUE);
			$sql = " select tu.*, GROUP_CONCAT( sc.school_name ) as 'school_name'  "
		        . ' FROM tutor tu , tutor_school tusc , school sc '
		        . ' where tu.tutor_id = tusc.tutor_id AND tusc.shool_id = sc.school_id  '
		        . " and tu.is_active = '1' " ;	
		    
			if($this->session->userdata('profile') != 'Administrator') {
				$sql .= " and tusc.shool_id in ( "
					 ." select eems.school_id "
					 ."	from employee eem, employee_school eems "
					 ."	where eem.employee_id = eems.employee_id "
					 ." and  (eems.tutor_information = 'Y' or eems.tutor_contract = 'Y')  "
					 ."	and eem.username = '". $this->session->userdata('username') ."' )  ";
	 		}
			if($key['name'] != null)
				$sql .= " and (first_name_th like '%".$key['name']."%' or last_name_th like '%".$key['name']."%' )";
			if($key['name_en'] != null)
				$sql .= " and (first_name_en like '%".$key['name_en']."%' or last_name_en like '%".$key['name_en']."%' )";
			if($key['nick_name'] != null)
				$sql .= " and (nick_name like '%".$key['nick_name']."%' )";
			if($key['tel'] != null)
				$sql .= " and (mobile01 like '%".$key['tel']."%' )";	
			if($key['email'] != null)
				$sql .= " and (email like '%".$key['email']."%' )";
			if($key['subject'] != null){
			    $sql .= " and (teach01_subject like '%". $key['subject'] ."%' or teach02_subject like '%". $key['subject'] 
			    	 ."%' or teach03_subject like '%". $key['subject'] ."%' or teach04_subject like '%". $key['subject'] 
			    	 ."%' or teach05_subject like '%". $key['subject'] ."%' or teach06_subject like '%". $key['subject']
			    	 ."%' or teach_other_1 like '%". $key['subject'] ."%' or teach_other_2 like '%". $key['subject'] 
			    	 ."%' or teach_other_5 like '%". $key['subject'] ."%' or teach_other_6 like '%". $key['subject'] 
			    	 ."%' or teach_other_3 like '%". $key['subject'] ."%' or teach_other_4  like '%". $key['subject']
			    	 ."%') ";
			}
			if($key['location'] != null)
		    	$sql .= " and (teach_area like '%" . $key['location'] . "%' ) "  ;
			if($key['school'] != null)
		    	$sql .= $key['school'] ;	
				
			$sql .= " GROUP BY tu.tutor_id order by first_name_th, last_name_th ";	
			$query = $this->db->query($sql);  
			return $query->result();
		}
		
		function approve($data) 
		{	
			$this->db->set('update_by', "'".$this->session->userdata('username')."'", FALSE);
			$this->db->set('update_date', 'NOW()', FALSE);
            $this->db->where('tutor_id', $data['tutor_id']);
			$this->db->update('tutor', $data);
		}
		
		function delete($newsID)
		{
			$data = array(
               'is_active' => 0,
			   'update_by' => $this->session->userdata('username')   
            );
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->where('tutor_id', $newsID);
			$this->db->update('tutor', $data); 
		}
	}
?>